ALTER PROC SP_CREATE_UPDATE_PROFORMA_INVOICE_DETAILS
(
	@INVOICE_ID 			INT,
	@INVOICE_NO 			VARCHAR(50),
	@INVOICE_DATE 			VARCHAR(20),
	@SALESORDER_ID			INT,	
	@SYSTEM_USER 			VARCHAR(50),
	@BANK_NAME 			VARCHAR(50),
	@ADDRESS_1 			VARCHAR(62),
	@ADDRESS_2 			VARCHAR(62),
	@ADDRESS_3 			VARCHAR(62),
	@ADDRESS_4 			VARCHAR(62),
	@CITY 				VARCHAR(50),
	@STATE 				VARCHAR(50),
	@ZIP_CODE			VARCHAR(50),
	@COUNTRY			VARCHAR(62),
	@ABA				VARCHAR(50),
	@ACCT				VARCHAR(50),
	@SWIFT_CODE			VARCHAR(50),
	@BANK_ID			INT,
	@OUT_INVOICE_ID 		INT OUT
)
as
begin
SET NOCOUNT ON
	DECLARE @ADDRESS_ID INT
	SELECT @OUT_INVOICE_ID = PROFORMA_INVOICE_ID FROM PROFORMA_INVOICE WHERE PROFORMA_INVOICE_ID = @INVOICE_ID
	
	IF( @@ROWCOUNT = 0 )
	BEGIN
		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TIMESTAMP )
		VALUES ( @ADDRESS_1, @ADDRESS_2, @ADDRESS_3, @ADDRESS_4, @CITY, @STATE, @ZIP_CODE, @COUNTRY, '', '', '', '', '', CURRENT_TIMESTAMP )	
	
		SELECT @ADDRESS_ID = @@IDENTITY
		INSERT INTO PROFORMA_INVOICE( PROFORMA_INVOICE_NO, PI_DATE, SALESORDER_ID, PI_CREATED_ON, PI_CREATED_BY, PI_BANK_NAME, PI_ABA, PI_ACCT, PI_SWIFT_CODE, ADDRESS_ID, STATUS, BANKID, TIMESTAMP )
		VALUES( @INVOICE_NO, CONVERT(VARCHAR, @INVOICE_DATE, 101), @SALESORDER_ID, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101), @SYSTEM_USER, @BANK_NAME, @ABA, @ACCT, @SWIFT_CODE, @ADDRESS_ID, 'ISSUED', @BANK_ID, CURRENT_TIMESTAMP )

		SELECT @OUT_INVOICE_ID = @@IDENTITY
	END
	ELSE
	BEGIN
		SELECT @ADDRESS_ID = ADDRESS_ID FROM PROFORMA_INVOICE WHERE PROFORMA_INVOICE_ID = @INVOICE_ID

		UPDATE ADDRESS SET
				ADDRESS_1 	= @ADDRESS_1, 
				ADDRESS_2 	= @ADDRESS_2, 
				ADDRESS_3 	= @ADDRESS_3,
				ADDRESS_4 	= @ADDRESS_4,  
				CITY		= @CITY, 
				STATE		= @STATE, 
				ZIPCODE		= @ZIP_CODE, 
				COUNTRY		= @COUNTRY,
				TIMESTAMP 	= CURRENT_TIMESTAMP
		WHERE ADDRESS_ID = @ADDRESS_ID

		UPDATE PROFORMA_INVOICE SET 
					PI_MODIFIED_ON 	= CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101), 
					PI_MODIFIED_BY 	= @SYSTEM_USER, 
					PI_BANK_NAME	= @BANK_NAME,
					PI_ABA		= @ABA,
					PI_ACCT		= @ACCT,
					PI_SWIFT_CODE	= @SWIFT_CODE,
					BANKID		= @BANK_ID,
					TIMESTAMP = CURRENT_TIMESTAMP  
		WHERE PROFORMA_INVOICE_ID = @INVOICE_ID
	END
END	
